package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Gui.Terminal;
import Model.Customer;
import Model.Invoice;
import Model.Salesorder;

public class DBSalesorder {
	
	private final String id = "SalesorderID";
	private final String date = "date";
	private final String deliveryStatus = "deliveryStatus";
	private final String deliveryDate = "deliveryDate";
	private final String invoice = "InvNo";
	private final String customer = "CustID";
	private final String selectAll = String.format("SELECT %s, %s, %s, %s, %s, %s FROM Salesorder", id, customer, invoice, date, deliveryStatus, deliveryDate);
	private Connection con;

	public DBSalesorder()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	public Salesorder getSalesorder(String id)
	{
		//Create statement
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("An error occured during statement creation", e);
		}
		
		//Execute statement
		try {
			stmt.execute(selectWhere(this.id + " = " + id));
		} catch (SQLException e) {
			Terminal.say("An error occured during execution of statement", e);
		}
		
		//Retrieve resultSet
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e) {
			Terminal.say("An error occured during retriving resultset", e);
		}
		
		Salesorder result = buildSalesorder(resSet);
		
		//Close statement
		try {
			stmt.close(); 
		} catch (SQLException e) {
			Terminal.say("An error occured during the closure of the statement");
		}
		
		return result;
	}
	
	public ArrayList<Salesorder> getAllSalesorders ()
	{
		ArrayList<Salesorder> result = new ArrayList<Salesorder>();
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("An error occured during statement creation", e);
		}
		
		//Execute statement
		try {
			stmt.execute(selectAll);
		} catch (SQLException e) {
			Terminal.say("An error occured during execution of statement", e);
		}
		
		//Retrieve resultSet
		ResultSet resSet = null;
		try {
			resSet = stmt.getResultSet();
		} catch (SQLException e1) {
			Terminal.say("An error occured during statement retrieving" ,e1);
		}
		try {
			while (resSet.next())
			{
				String id = resSet.getString(this.id);
				String date = resSet.getString(this.date);
				String deliveryStatus = resSet.getString(this.deliveryStatus);
				String deliveryDate = resSet.getString(this.deliveryDate);
				DBInvoice dbi = new DBInvoice();
				Invoice invoice = dbi.getInvoice(resSet.getString(this.invoice));
				DBCustomer dbc = new DBCustomer();
				Customer customer = dbc.getCustomer(resSet.getString(this.customer));
				
				result.add(new Salesorder(id, date, deliveryStatus, deliveryDate, invoice, customer));
			}
		} catch (SQLException e) {
			Terminal.say("An error occured during retriving resultset", e);
		}
		
		//Close statement
		try {
			stmt.close(); 
		} catch (SQLException e) {
			Terminal.say("An error occured during the closure of the statement");
		}
		
		return result;
	}
	
	private String selectWhere(String condition)
	{
		return selectAll + " WHERE " + condition;
	}
	
	
	
	private Salesorder buildSalesorder(ResultSet result)
	{
		try {
			if(result.next())
			{
				String id = result.getString(this.id);
				String date = result.getString(this.date);
				String deliveryStatus = result.getString(this.deliveryStatus);
				String deliveryDate = result.getString(this.deliveryDate);
				DBInvoice dbi = new DBInvoice();
				Invoice invoice = dbi.getInvoice(result.getString(this.invoice));
					
				DBCustomer dbc = new DBCustomer();
				Customer customer = dbc.getCustomer(result.getString(this.customer));
				return new Salesorder(id, date, deliveryStatus, deliveryDate, invoice, customer);
			}
		} catch (SQLException e) {
			Terminal.say("An error occured while building a Salesorder object", e);
		}
		return null;
	}
	
	public boolean addSalesorder(Salesorder salesorder)
	{
		boolean succes = true;
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("An error occured during statement creation", e);
		}
		
		//Execute statement
		String id = salesorder.getId();
		String date = salesorder.getDate();		
		String deliveryStatus = salesorder.getDeliveryStatus();
		String deliveryDate = salesorder.getDeliveryDate();
		String invoice = salesorder.getInvoice().getInvoiceNo();
		String customer = salesorder.getCustomer().getId();
		try {
			stmt.execute(String.format("INSERT INTO Salesorder VALUES %s, %s, %s, %s, %s, %s", id, date, deliveryStatus, deliveryDate, invoice, customer));
		} catch (SQLException e) {
			Terminal.say("An error occured while inserting a Salesorder object", e);
			succes = false;
		}
		
		return succes;
	}
	
	public boolean deleteSalesorder(String id)
	{
		boolean succes = true;
		
		//Create statement
		Statement stmt = null;
		try {
			stmt = con.createStatement();
		} catch (SQLException e) {
			Terminal.say("An error occured during statement creation", e);
		}
		
		//Execute statement
		try {
			stmt.execute(String.format("DELETE FROM Salesorder WHERE SalesorderID = %s", this.id));
		} catch (SQLException e) {
			Terminal.say("An error occured during removing a Salesorder object from the database", e);
			succes = false;
		}
		
		//Close statement 
		try {
			stmt.close();
		} catch (SQLException e) {
			Terminal.say("An error occured during statment closing",e);
		}
		
		return succes;
	}	
	
}


